/*
 * Copyright (C) 2013 SeekDeepWithin.com
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */
package com.seekdeepwithin.DataAccess;

import com.almworks.sqlite4java.*;
import java.io.*;
import java.util.*;
import javafx.application.*;
import javafx.concurrent.*;

/**
 * User: Jonathan Montiverdi Date: Sep 27, 2013
 *
 * Service used to create a new database.
 */
public class CreateDatabaseService extends Service<Void>
{
   @Override
   protected Task<Void> createTask ()
   {
      return new Task<Void> ()
      {
         @Override
         protected Void call () throws Exception
         {
            updateMessage ("Creating database");
            updateProgress (1, 10);
            SQLiteConnection db = new SQLiteConnection (new File (SdwDatabase.DB_PATH));
            db.open ();
            db.exec ("CREATE TABLE areas (area_id INTEGER UNIQUE, area TEXT);");
            db.exec ("CREATE TABLE books (book_id INTEGER UNIQUE, is_subbook NUMERIC, title TEXT, subtitle TEXT, abbreviation TEXT, author TEXT, date TEXT, amazonid TEXT, has_chapters NUMERIC, has_subbooks NUMERIC, image TEXT, info TEXT);");
            db.exec ("CREATE TABLE book_sources (book_id INTEGER, source_id INTEGER, PRIMARY KEY (book_id, source_id));");
            db.exec ("CREATE TABLE book_translators (book_id INTEGER, translator_id INTEGER, PRIMARY KEY (book_id, translator_id));");
            db.exec ("CREATE TABLE chapters (chapter_id INTEGER UNIQUE, name TEXT);");
            db.exec ("CREATE TABLE chapter_footers (chapter_list_id INTEGER UNIQUE, footer_id INTEGER);");
            db.exec ("CREATE TABLE chapter_headers (chapter_list_id INTEGER UNIQUE, header_id INTEGER);");
            db.exec ("CREATE TABLE chapter_list (chapter_list_id INTEGER UNIQUE, sub_book_list_id INTEGER, chapter_id INTEGER, order_index INTEGER);");
            db.exec ("CREATE TABLE footers (footer_id INTEGER UNIQUE, footer TEXT);");
            db.exec ("CREATE TABLE glossary_entries (glossary_entry_id INTEGER UNIQUE, glossary_entry TEXT);");
            db.exec ("CREATE TABLE glossary_entry_definitions (glossary_entry_id INTEGER, start_index INTEGER, end_index INTEGER, glossary_item_id INTEGER, PRIMARY KEY (glossary_item_id, glossary_entry_id));");
            db.exec ("CREATE TABLE glossary_entry_footers (glossary_entry_id INTEGER, footer_id INTEGER, footer_index INTEGER, PRIMARY KEY (glossary_entry_id, footer_id));");
            db.exec ("CREATE TABLE glossary_entry_headers (glossary_entry_id INTEGER UNIQUE, header_id INTEGER);");
            db.exec ("CREATE TABLE glossary_entry_links (glossary_entry_id INTEGER, start_index INTEGER, end_index INTEGER, link_id INTEGER, PRIMARY KEY (glossary_entry_id, link_id));");
            db.exec ("CREATE TABLE glossary_entry_styles (glossary_entry_style_id INTEGER UNIQUE, glossary_entry_id INTEGER, start_index INTEGER, end_index INTEGER, style_id INTEGER);");
            db.exec ("CREATE TABLE glossary_items (glossary_item_id INTEGER UNIQUE, glossary_item TEXT);");
            db.exec ("CREATE TABLE glossary_item_links (glossary_item_id INTEGER, link_id INTEGER, PRIMARY KEY (glossary_item_id, link_id));");
            db.exec ("CREATE TABLE glossary_list (glossary_item_id INTEGER, glossary_entry_id INTEGER, order_index INTEGER, PRIMARY KEY (glossary_item_id, glossary_entry_id));");
            db.exec ("CREATE TABLE glossary_see_also (glossary_item_id INTEGER, link_id INTEGER, PRIMARY KEY (glossary_item_id, link_id));");
            db.exec ("CREATE TABLE glossary_sources (glossary_item_id INTEGER UNIQUE, source_id INTEGER);");
            db.exec ("CREATE TABLE headers (header_id INTEGER UNIQUE, header TEXT);");
            db.exec ("CREATE TABLE links (link_id INTEGER UNIQUE, area_id TEXT, link TEXT);");
            db.exec ("CREATE TABLE passages (passage_id INTEGER UNIQUE, passage TEXT);");
            db.exec ("CREATE TABLE passage_definitions (passage_id INTEGER, start_index INTEGER, end_index INTEGER, glossary_item_id INTEGER, PRIMARY KEY (passage_id, glossary_item_id));");
            db.exec ("CREATE TABLE passage_footers (passage_id INTEGER, footer_id INTEGER, footer_index INTEGER, PRIMARY KEY (passage_id, footer_id));");
            db.exec ("CREATE TABLE passage_headers (passage_id INTEGER UNIQUE, header_id INTEGER);");
            db.exec ("CREATE TABLE passage_links (passage_id INTEGER, start_index INTEGER, end_index INTEGER, link_id INTEGER, PRIMARY KEY (passage_id, link_id));");
            db.exec ("CREATE TABLE passage_list (chapter_list_id INTEGER, passage_id INTEGER, passage_number INTEGER, order_index INTEGER, PRIMARY KEY (chapter_list_id, passage_id));");
            db.exec ("CREATE TABLE passage_styles (passage_style_id INTEGER UNIQUE, passage_id INTEGER, start_index INTEGER, end_index INTEGER, style_id INTEGER);");
            db.exec ("CREATE TABLE sources (source_id INTEGER UNIQUE, source_name_id INTEGER, source TEXT);");
            db.exec ("CREATE TABLE source_names (source_name_id INTEGER UNIQUE, source_name TEXT UNIQUE);");
            db.exec ("CREATE TABLE styles (style_id INTEGER UNIQUE, style_type_id INTEGER, style TEXT);");
            db.exec ("CREATE TABLE style_types (style_type_id INTEGER UNIQUE, style_type TEXT UNIQUE);");
            db.exec ("CREATE TABLE subbook_list (sub_book_list_id INTEGER UNIQUE, book_id INTEGER, sub_book_id INTEGER, order_index INTEGER);");
            db.exec ("CREATE TABLE subbook_list_links (sub_book_list_id INTEGER UNIQUE, sub_book_list_id_link INTEGER);");
            db.exec ("CREATE TABLE translators (translator_id INTEGER UNIQUE, translator TEXT UNIQUE);");
            updateProgress (3, 10);

            // Add areas...
            SQLiteStatement statement = db.prepare ("INSERT INTO areas (area_id, area) VALUES (?,?);");
            Map<Integer, String> allAreas = SdwApi.getInstance ().getAllAreas ();
            for (Map.Entry<Integer, String> area : allAreas.entrySet ()) {
               Integer areaId = area.getKey ();
               String areaName = area.getValue ();
               statement.bind (1, areaId);
               statement.bind (2, areaName);
               statement.step ();
               statement.reset ();
            }
            statement.dispose ();
            updateProgress (6, 10);

            // Add style types...
            statement = db.prepare ("INSERT INTO style_types (style_type_id, style_type) VALUES (?,?);");
            Map<Integer, String> allStyleTypes = SdwApi.getInstance ().getAllStyleTypes ();
            for (Map.Entry<Integer, String> styleType : allStyleTypes.entrySet ()) {
               Integer styleTypeId = styleType.getKey ();
               String styleTypeName = styleType.getValue ();
               statement.bind (1, styleTypeId);
               statement.bind (2, styleTypeName);
               statement.step ();
               statement.reset ();
            }
            statement.dispose ();
            db.dispose ();
            updateProgress (10, 10);
            updateMessage ("Completed!");
            Platform.runLater (new Runnable ()
            {
               @Override public void run ()
               {
                  SdwDatabase.getInstance ().update ();
               }
            });
            return null;
         }
      };
   }
}
